package com.myh.exam.dao;

import com.myh.exam.dto.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    //1.新增
    public Integer addUser(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = getConnection();
            preparedStatement = connection.prepareStatement("INSERT INTO `b_user`( `name`, `passwd`, `nick_name`) VALUES ( ?,?,?)");
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPasswd());
            preparedStatement.setString(3, user.getNickName());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeDB(connection, preparedStatement, resultSet);
        }
        return 0;
    }

    //2.查询
    public List<User> selectUserList(User user, Integer start, Integer row) {
        List<User> result = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = getConnection();
            StringBuilder sql = new StringBuilder("select * from b_user where 1=1");
            if (user.getId() != null) {
                sql.append(" and id=? ");
            }
            if (user.getName() != null && !user.getName().trim().equals("")) {
                sql.append(" and name=? ");
            }
            if (start != null) {
                sql.append(" limit ?");
                if (row != null) {
                    sql.append(" ,? ");
                }
            }
            preparedStatement = connection.prepareStatement(sql.toString());
            int i = 0;
            if (user.getId() != null) {
                preparedStatement.setInt(++i, user.getId());
            }
            if (user.getName() != null && !user.getName().trim().equals("")) {
                preparedStatement.setString(++i, user.getName());
            }
            if (start != null) {
                preparedStatement.setInt(++i, start);
                if (row != null) {
                    preparedStatement.setInt(++i, row);
                }
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User userTemp = new User();
                userTemp.setId(resultSet.getInt("id"));
                userTemp.setName(resultSet.getString("name"));
                userTemp.setNickName(resultSet.getString("nick_name"));
                userTemp.setPasswd(resultSet.getString("passwd"));
                result.add(userTemp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeDB(connection, preparedStatement, resultSet);
        }
        return result;

    }


    //3.更新
    public Integer updateUser(User user) {
        if (user.getId() == null) {
            return 0;
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = getConnection();
            preparedStatement = connection.prepareStatement("UPDATE `b_user` SET `name` = ?, `passwd` = ?, `nick_name` = ? WHERE `id` =?;");
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPasswd());
            preparedStatement.setString(3, user.getNickName());
            preparedStatement.setInt(4, user.getId());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeDB(connection, preparedStatement, resultSet);
        }
        return 0;

    }

    //4.删除
    public Integer delUser(Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = getConnection();
            preparedStatement = connection.prepareStatement("delete from `b_user` where id =?");
            preparedStatement.setInt(1, id);
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeDB(connection, preparedStatement, resultSet);
        }
        return 0;

    }

    /**
     * 获取一个数据库连接
     *
     * @return
     * @throws SQLException
     */

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/java_web_exam?serverTimezone=Asia/Shanghai&useSSL=false", "stu", "111111");
    }

    /**
     * 关闭数据库连接
     *
     * @param connection
     * @param preparedStatement
     * @param resultSet
     */
    private void closeDB(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.closeOnCompletion();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
